---
title: "Acala / Karura Dashboards"
output:
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: scroll
    social: menu
    source_code: embed
params:
  endpoint: "https://api.subquery.network/sq/rogerjbos/karura-swap-day-data"
  network: Karura
  window: 14
---

```{css custom1, echo=FALSE}
.dataTables_scrollBody {
    max-height: 100% !important;
}
```

```{r global, include=FALSE}

knitr::opts_chunk$set(
  message = FALSE,
  warning = FALSE,
  comment = "#>"
)

library(kableExtra)
library(formattable)
library(lubridate)
library(flexdashboard)
library(DT)

# Helper function to concat
`%+%` <- function(a, b) paste0(a, b)

fixToken <- function(x) {
  x <- gsub('fa://0', 'RMRK', x)
  x <- gsub('lc://13', 'LCDOT', x)
  x <- gsub('sa://0', 'taiKSM', x)
  x
}

tokens <- rbind(c("ACA", "Acala", 12),
                c("AUSD","Acala Dollar", 12),
                c("taiKSM","Taiga KSM", 12),
                c("DOT","Polkadot", 10),
                c("LCDOT","Liquid Crowdloan DOT", 10),
                c("LDOT","Liquid DOT", 10),
                c("RENBTC","Ren Protocol BTC", 8),
                c("CASH","Compound CASH", 8),
                c("KAR","Karura", 12),
                c("KUSD","Karura Dollar", 12),
                c("KSM","Kusama", 12),
                c("LKSM","Liquid KSM", 12),
                c("TAI","Taiga", 12),
                c("BNC","Bifrost Asgard", 12),
                c("VSKSM","Bifrost Voucher Slot KSM", 12),
                c("PHA","Phala Native Token", 12),
                c("KINT","Kintsugi Native Token", 12),
                c("KBTC","Kintsugi Wrapped BTC", 8)) %>%
  as.data.table %>%
  setnames(c("Token","Name","decimals"))


# remotes::install_github("ropensci/ghql") # if package is not already installed
library(jsonlite)
library(data.table)
library(ghql)
x <- GraphqlClient$new()


window <- params$window
endpoint <- params$endpoint
network <- params$network

if (network == 'Acala') {
  
  dex_endpoint <- "https://api.subquery.network/sq/rogerjbos/acala-dex-subql"
  loan_endpoint <- "https://api.subquery.network/sq/rogerjbos/acala-loan-subql"
  swap_endpoint <- "https://api.subquery.network/sq/rogerjbos/acala-swap-day-data"
  official_endpoint <- "https://api.subquery.network/sq/AcalaNetwork/acala"
  
} else {

  dex_endpoint <- "https://api.subquery.network/sq/rogerjbos/karura-dex-subql"
  loan_endpoint <- "https://api.subquery.network/sq/rogerjbos/karura-loan-subql"
  swap_endpoint <- "https://api.subquery.network/sq/rogerjbos/karura-swap-day-data"
  official_endpoint <- "https://api.subquery.network/sq/AcalaNetwork/karura"

}

getSwaps <- function(endpoint, window) {
  # endpoint <- acala_endpoint; window <- 10
  
  # make a client
  cli <- GraphqlClient$new(url = endpoint)
  mindate <- today(tzone = 'UTC') - window
  
  cursor <- ''
  resList <- list()
  for (i in 1:1000) {
    if (cursor == '') {
      cursorStr <- 'first:100'
    } else {
      cursorStr <- 'first:100 after:"' %+% cursor %+% '"'
    }
    qry <- Query$new()
    qry$query('dexActions', '
    {
      query {
        dexActions (filter: {timestamp: {greaterThanOrEqualTo: "' %+% mindate %+% '"}, type: {equalTo: "swap"}} ' %+% cursorStr %+% ') {
          totalCount
          edges {
            node { timestamp id accountId token0Id token1Id  volumeUSD volumeUSDFloat pathLength data
            }
            cursor
          }
          pageInfo {
            endCursor
            hasNextPage
          }
        }
      }
    }')
    result <- cli$exec(qry$queries$dexActions)  %>%
      fromJSON(flatten=TRUE)
    cursor <- result$data$query$dexActions$pageInfo$endCursor
    res <- as.data.table(result$data$query$dexActions$edges)
    res[, cursor := NULL]
    
    print(i %+% " " %+% nrow(res))
    resList[[i]] <- res
    if (result$data$query$dexActions$pageInfo$hasNextPage == FALSE) break
  }
  res <- rbindlist(resList)
  setnames(res, old = names(res), new = gsub("node.", "", names(res)))
  
  if (substr(max(res$timestamp), 12, 13) < 23) {
    maxdate <- as.Date(max(res$timestamp))-1
  } else {
    maxdate <- as.Date(max(res$timestamp))
  }
  
  res <- res[timestamp <= maxdate]
  res[, date := as.Date(timestamp)]
  setorder(res, timestamp)
  
  # Replace foreign assets
  res[, token0Id := fixToken(token0Id)]
  res[, token1Id := fixToken(token1Id)]

  if (min(res$pathLength) == 2) res[, pathLength := pathLength - 1]
  res[, fee := .003 * volumeUSDFloat]
  res[, feeAdj := .003 * pathLength * volumeUSDFloat]

  # Normalize pairs
  res[, pair := paste0(token0Id %+% ":" %+% token1Id)]
  res[token1Id < token0Id, pair := paste0(token1Id %+% ":" %+% token0Id)]
  res[, exclude := token0Id == token1Id]
  res  
  
}

getSwapsByDay <- function(endpoint, window) {
  # endpoint <- karura_swap_endpoint; window <- 40
  
  # make a client
  cli <- GraphqlClient$new(url = endpoint)
  mindate <- today(tzone = 'UTC') - window
  
  cursor <- ''
  resList <- list()
  for (i in 1:1000) {
    if (cursor == '') {
      cursorStr <- 'first:100'
    } else {
      cursorStr <- 'first:100 after:"' %+% cursor %+% '"'
    }
    qry <- Query$new()
    qry$query('swapDayData', '
      {
        query {
          swapDayData (filter: {date: {greaterThanOrEqualTo: "' %+% mindate %+% '"}} ' %+% cursorStr %+% ') {
            totalCount
            edges {
              node { 
                date
                token0Id 
                token1Id 
                volumeUSD
                tvlUSD
                feeUSD
                txCount
              }
              cursor
            }
            pageInfo {
              endCursor
              hasNextPage
            }
          }
        }
      }')
      result <- cli$exec(qry$queries$swapDayData)  %>%
        fromJSON(flatten=TRUE)
    
      cursor <- result$data$query$swapDayData$pageInfo$endCursor
      res <- as.data.table(result$data$query$swapDayData$edges)
      res[, cursor := NULL]
      
      print(i %+% " " %+% nrow(res))
      resList[[i]] <- res
      if (result$data$query$swapDayData$pageInfo$hasNextPage == FALSE) break
  }
  res <- rbindlist(resList)
  setnames(res, old = names(res), new = gsub("node.", "", names(res)))
  res[, date := as.Date(date)]
  setorder(res, date)
  
  # Replace foreign assets
  res[, token0Id := fixToken(token0Id)]
  res[, token1Id := fixToken(token1Id)]

  # Normalize pairs
  res[, pair := paste0(token0Id %+% ":" %+% token1Id)]
  res[token1Id < token0Id, pair := paste0(token1Id %+% ":" %+% token0Id)]
  res  
  
}


liquidityQuery <- function(endpoint, window) {
  method <- 'dexActions'; maxn <- 1000

  cli <- GraphqlClient$new(url = endpoint)
  mindate <- today(tzone = 'UTC') - window
  
  cursor <- ''
  resList <- list()
  for (i in 1:maxn) {
    if (cursor == '') {
      cursorStr <- 'first:100'
    } else {
      cursorStr <- 'first:100 after:"' %+% cursor %+% '"'
    }
    qry <- Query$new()
    qry$query(method, '
      {
        query {
          ' %+% method %+% ' (filter: {timestamp: {greaterThanOrEqualTo: "' %+% mindate %+% '"}, type: {in: ["addLiquidity","removeLiquidity"]}} ' %+% cursorStr %+% ') {
            totalCount
            edges {
              node { timestamp id nodeId accountId type token0Id token1Id token0Amount token1Amount volumeUSD
              }
              cursor
            }
            pageInfo {
              endCursor
              hasNextPage
            }
          }
        }
      }')
    result <- cli$exec(qry$queries[[method]])  %>%
      fromJSON(flatten=TRUE)
    cursor <- result$data$query[[method]]$pageInfo$endCursor
    res <- as.data.table(result$data$query[[method]]$edges)
    res[, cursor := NULL]
    
    print(i %+% " " %+% nrow(res))
    resList[[i]] <- res
    if (result$data$query[[method]]$pageInfo$hasNextPage == FALSE) break
  }
  res <- rbindlist(resList) %>% 
    setnames(names(res), gsub("node.", "", names(res)))
  res[, date := as.Date(timestamp)]
  setorder(res, date)
  
  # Replace foreign assets
  res[, token0Id := fixToken(token0Id)]
  res[, token1Id := fixToken(token1Id)]

  # Normalize pairs
  res[, pair := paste0(token0Id %+% ":" %+% token1Id)]
  res[token1Id < token0Id, pair := paste0(token1Id %+% ":" %+% token0Id)]
  res  
  
}

getLoansCollateralParamsQuery <- function(endpoint) {
  method <- 'collateralParams'; maxn <- 1000
  
  cli <- GraphqlClient$new(url = endpoint)

  qry <- Query$new()
  qry$query(method, '
    {
      query {
     	collateralParams {
          totalCount
            nodes { 
							collateral {
							  id
							} maximumTotalDebitValue interestRatePerSec liquidationRatio 
              liquidationPenalty requiredCollateralRatio 
          }
        }
      }
    }')
  result <- cli$exec(qry$queries[[method]])  %>%
    fromJSON(flatten=TRUE)
  res <- as.data.table(result$data$query[[method]]$nodes)

  # Replace foreign assets
  res[, collateral.id := fixToken(collateral.id)]
  res <- merge(res, tokens, by.x='collateral.id', by.y='Token')

  res[, adj := as.numeric(substr(as.character(1e20),1, as.numeric(decimals) + 1))]
  res[, maximumTotalDebitValue := as.numeric(maximumTotalDebitValue) / as.numeric(adj)]  
  res[, liquidationRatio := as.numeric(liquidationRatio) / 1e18]  
  res[, liquidationPenalty := as.numeric(liquidationPenalty) / 1e18]  
  res[, requiredCollateralRatio := as.numeric(requiredCollateralRatio) / 1e18]  
  res[, APR := (as.numeric(interestRatePerSec) / 1e18 + 1) ** (365 * 86400) - 1]
  res

}

getLoansDailyPositionsQuery <- function(endpoint, window) {
  method <- 'dailyPositions'; maxn <- 1000

  cli <- GraphqlClient$new(url = endpoint)
  mindate <- today(tzone = 'UTC') - window
  
  cursor <- ''
  resList <- list()
  for (i in 1:maxn) {
    if (cursor == '') {
      cursorStr <- 'first:100'
    } else {
      cursorStr <- 'first:100 after:"' %+% cursor %+% '"'
    }
    qry <- Query$new()
    qry$query(method, '
      {
        query {
          ' %+% method %+% ' (filter: {timestamp: {greaterThanOrEqualTo: "' %+% mindate %+% '"}} ' %+% cursorStr %+% ') {
            totalCount
            edges {
              node { 
               owner {id} collateral {id} depositVolumeUSD debitVolumeUSD 
               depositChangedUSD debitChangedUSD debitExchangeRate timestamp txCount
              }
              cursor
            }
            pageInfo {
              endCursor
              hasNextPage
            }
          }
        }
      }')
    result <- cli$exec(qry$queries[[method]])  %>%
      fromJSON(flatten=TRUE)
    cursor <- result$data$query[[method]]$pageInfo$endCursor
    res <- as.data.table(result$data$query[[method]]$edges)
    res[, cursor := NULL]
    
    print(i %+% " " %+% nrow(res))
    resList[[i]] <- res
    if (result$data$query[[method]]$pageInfo$hasNextPage == FALSE) break
  }
  res <- rbindlist(resList) %>% 
    setnames(names(res), gsub("node.", "", names(res)))
  res[, Date := as.Date(timestamp)]
  res[, collateral.id := fixToken(collateral.id)]

  res[, depositVolumeUSD := as.numeric(depositVolumeUSD) / 1e18]
  res[, debitVolumeUSD := as.numeric(debitVolumeUSD) / 1e18]
  res[, depositChangedUSD := as.numeric(depositChangedUSD) / 1e18]
  res[, debitChangedUSD := as.numeric(debitChangedUSD) / 1e18]
  res[, debitExchangeRate := as.numeric(debitExchangeRate) / 1e18]
  res
  
}


getLoansDailyCollateralQuery <- function(endpoint, window) {
  method <- 'dailyCollaterals'; maxn <- 1000
  
  cli <- GraphqlClient$new(url = endpoint)
  mindate <- today(tzone = 'UTC') - window
  
  
  cursor <- ''
  resList <- list()
  for (i in 1:maxn) {
    if (cursor == '') {
      cursorStr <- 'first:100'
    } else {
      cursorStr <- 'first:100 after:"' %+% cursor %+% '"'
    }
    qry <- Query$new()
    qry$query(method, '
      {
        query {
          ' %+% method %+% ' (filter: {timestamp: {greaterThanOrEqualTo: "' %+% mindate %+% '"}} ' %+% cursorStr %+% ') {
            totalCount
            edges {
              node { 
               collateral {id} depositVolumeUSD debitVolumeUSD 
               depositChangedUSD debitChangedUSD debitExchangeRate timestamp txCount
              }
              cursor
            }
            pageInfo {
              endCursor
              hasNextPage
            }
          }
        }
      }')
    result <- cli$exec(qry$queries[[method]])  %>%
      fromJSON(flatten=TRUE)
    cursor <- result$data$query[[method]]$pageInfo$endCursor
    res <- as.data.table(result$data$query[[method]]$edges)
    res[, cursor := NULL]
    
    print(i %+% " " %+% nrow(res))
    resList[[i]] <- res
    if (result$data$query[[method]]$pageInfo$hasNextPage == FALSE) break
  }
  res <- rbindlist(resList) %>% 
    setnames(names(res), gsub("node.", "", names(res)))
  res[, Date := as.Date(timestamp)]
  res[, collateral.id := fixToken(collateral.id)]
  
  res[, depositVolumeUSD := as.numeric(depositVolumeUSD) / 1e18]
  res[, debitVolumeUSD := as.numeric(debitVolumeUSD) / 1e18]
  res[, depositChangedUSD := as.numeric(depositChangedUSD) / 1e18]
  res[, debitChangedUSD := as.numeric(debitChangedUSD) / 1e18]
  res[, debitExchangeRate := as.numeric(debitExchangeRate) / 1e18]
  res

}



# queries ####


dailyLoanCollateral <- getLoansDailyCollateralQuery(loan_endpoint, window)
dailyLoanPositions <- getLoansDailyPositionsQuery(loan_endpoint, window)
collaterParams <- getLoansCollateralParamsQuery(loan_endpoint)


liquidity <- liquidityQuery(swap_endpoint, window)
liquidity[, volumeUSD := as.numeric(volumeUSD) / 1e18]
liq <- liquidity[, .(date, pair, type, volumeUSD)]
liq2 <- liq[, .(.N, sum(volumeUSD)), by = .(pair, date, type)]
setnames(liq2, c("date","N","V2"), c("Date","Observations","volumeUSD"))
liq2[type == 'removeLiquidity', volumeUSD := -volumeUSD]


liq <- liq[, .(.N, sum(volumeUSD)), by = .(date, type)]
setnames(liq, c("date","N","V2"), c("Date","Observations","volumeUSD"))
liq[type == 'removeLiquidity', volumeUSD := -volumeUSD]

swaps2 <- getSwapsByDay(swap_endpoint, window)
swaps <- getSwaps(swap_endpoint, window)

d <- list()
for (i in 1:nrow(swaps)) {
  
  tmp <- swaps[['data']][[i]][['value']]
  swapAccount <- tmp[[1]]
  test <- fromJSON(tmp[[2]])
  token <- test[[1]]
  if (length(test) > 1) {
    alt <- test[[2]]
    token[is.na(token)] <- alt[is.na(token)]  
  }
  token <- sub("13", "LCDOT", token)
  token <- sub("0", "RMRK", token)
  amount <- fromJSON(tmp[[3]])
  while (length(token) < 4) {
    token <- c(token, NA)
    amount <- c(amount, NA)
  }
  out <- data.table(swapAccount, t(token), t(amount)) %>%
    setnames(c("swapAccount", "token0", "token1", "token2", "token3", "amount0", "amount1", "amount2", "amount3"))
  d[[i]] <- out
  
}
out <- rbindlist(d)
swaps <- cbind(swaps, out)
swaps[, data := NULL]

swaps[, pair1 := paste0(token0 %+% ":" %+% token1)]
swaps[, pair2 := paste0(token1 %+% ":" %+% token2)]
swaps[, pair3 := paste0(token2 %+% ":" %+% token3)]
swaps[token1 < token0, pair1 := paste0(token1 %+% ":" %+% token0)]
swaps[token2 < token1, pair2 := paste0(token2 %+% ":" %+% token1)]
swaps[token3 < token2, pair3 := paste0(token3 %+% ":" %+% token2)]

# swaps[pair1=='ACA:AUSD' | pair2=='ACA:AUSD' | pair3=='ACA:AUSD', .N, by = date]

pairs <- rbind(swaps[exclude == FALSE, .N, by = pair1] %>% setnames("pair1", "Pair"),
              swaps[exclude == FALSE, .N, by = pair2] %>% setnames("pair2", "Pair"),
              swaps[exclude == FALSE, .N, by = pair3] %>% setnames("pair3", "Pair"))
pairs <- pairs[, sum(N), by = Pair]
# remove pairs with NA in them
pairs <- pairs[-grep("NA", pairs$Pair)]
pairs <- rbind(data.table(Pair = "ALL", V1 = sum(pairs$V1)), pairs)
pairs <- pairs[order(V1, decreasing = TRUE)] %>%
  setnames(c("Pair", "Observations"))

swaps[, paths := as.factor(pathLength)]

# Calculate measures for each pair
user_status   <- list()
trades_status <- list()
tpu_status    <- list()
volume_status <- list()

users_list  <- list()
trades_list <- list()
per_list    <- list()
volume_list <- list()

# remove old params object before calling render with new params list
rm(params)
for (p in pairs$Pair) {
  # p <- pairs$Pair[10]
  
  try(rm(u_list, t_list, p_list, v_list), silent = TRUE)
  
  outname <- "~/R_HOME/websites/web_acala/content/swap_" %+% network %+% "_" %+% p %+% ".html"
  unlink(outname)
  # Create report for each pair
  rmarkdown::render("~/R_HOME/karura-reports/Swap_template.Rmd",
                  output_file = outname,
                  params = list(pair = p))
  
  # Store the data for the table
  user_status[p]   <- activeUsersStatus
  trades_status[p] <- tradesStatus
  tpu_status[p]    <- avgTradeStatus
  volume_status[p] <- tradeVolumeStatus
  
  users_list[[p]]  <- u_list
  trades_list[[p]] <- t_list
  per_list[[p]]    <- p_list
  volume_list[[p]] <- v_list

}

  d <- list()
  for (x in pairs$Pair) {
    d[x] <- paste0('', x, '', collapse = '')
  }

  inline_plot <- data.frame(Count = pairs$Observations, 
                            U_Growth = unlist(user_status),
                            U_Trend = "",
                            S_Growth = unlist(trades_status),
                            S_Trend = "",
                            TPU_Growth = unlist(tpu_status),
                            TPU_Trend = "",
                            V_Growth = unlist(volume_status),
                            V_Trend = "")
  row.names(inline_plot) <- unlist(d)

  getSpecColor <- function(x) {
    x[is.na(x)] <- 0
    ifelse(x < 1, "red", "green")
  }
  
  inline_plot$U_Growth <- cell_spec(inline_plot$U_Growth, color = getSpecColor(inline_plot$U_Growth))

  inline_plot$S_Growth <- cell_spec(inline_plot$S_Growth, color = getSpecColor(inline_plot$S_Growth))
  
  inline_plot$TPU_Growth <- cell_spec(inline_plot$TPU_Growth, color = getSpecColor(inline_plot$TPU_Growth))

  inline_plot$V_Growth <- cell_spec(inline_plot$V_Growth, color = getSpecColor(inline_plot$V_Growth))

  p <- inline_plot %>%
    kbl(booktabs = TRUE, escape = FALSE, align='rrrrrrrrr') %>%
    add_header_above(c(" " = 1, " " = 1, "Active Users" = 2, "Swap Trades" = 2, "Trades Per User" = 2, "Trading Volume" = 2)) %>%  
    kable_paper(full_width = FALSE) %>%
    column_spec(4, image = spec_plot(users_list, same_lim = FALSE)) %>%
    column_spec(6, image = spec_plot(trades_list, same_lim = FALSE)) %>%
    column_spec(8, image = spec_plot(per_list, same_lim = FALSE)) %>%
    column_spec(10, image = spec_plot(volume_list, same_lim = FALSE))

```

### `r network` Swap Performance Summary

```{r plot_acala, result='asis', out.height = 12}
p

```

Last updated: `r Sys.time()`

Date range of data: `r min(swaps$timestamp)` to `r max(swaps$timestamp)`.

Sources: 
[SubQuery Network](https://explorer.subquery.network/)
Swaps:
* [Acala-Swap-Day-Data](https://api.subquery.network/sq/rogerjbos/acala-swap-day-data)
* [Karura-Swap-Day-Data](https://api.subquery.network/sq/rogerjbos/karura-swap-day-data)
Loans:
* [Acala-Loan-Data](https://api.subquery.network/sq/rogerjbos/acala-loan-subql)
* [Karura-Loan-Data](https://api.subquery.network/sq/rogerjbos/karura-loan-subql)